package com.qst.itoffer.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.qst.itoffer.bean.Applicant;
import com.qst.itoffer.bean.Company;
import com.qst.itoffer.bean.Job;
import com.qst.itoffer.bean.JobApply;
import com.qst.itoffer.bean.ResumeBasicinfo;
import com.qst.itoffer.util.DBUtil;

/**
 * 职位申请信息数据库操作类
 * @公司 青软实训
 * @作者 fengjj
 */
public class JobApplyDAO {
    public List<JobApply> query(String companyId, String jobId,
                                String startDate, String endDate) {
        List<JobApply> list = new ArrayList<JobApply>();
        Connection conn = DBUtil.getConnection();
        Statement stmt = null;
        ResultSet rs = null;
        StringBuffer sql = new StringBuffer("SELECT a.apply_id,a.apply_state,a.apply_date,"
                +" j.job_id,j.job_name,c.applicant_id,d.basicinfo_id,d.realname "
                +" FROM tb_jobapply a INNER JOIN tb_job j on a.job_id=j.job_id "
                +" INNER JOIN tb_applicant c on a.applicant_id=c.applicant_id  "
                +" INNER JOIN tb_resume_basicinfo d on c.applicant_id=d.applicant_id"
                +" WHERE 1=1 ");
        try {
            stmt = conn.createStatement();
            int cid = Integer.parseInt(companyId==null ? "0" :companyId);
            int jid = Integer.parseInt(jobId==null ? "0" :jobId);
            if(cid != 0)
                sql.append(" and j.company_id = " + cid);
            if(jid != 0)
                sql.append(" and a.job_id = "+jid);
            if(!"".equals(startDate))
                sql.append(" and a.apply_date >= '"+startDate+"'");
            if(!"".equals(endDate))
                sql.equals(" and a.apply_date <= '"+endDate+"'");
            rs = stmt.executeQuery(sql.toString());
            System.out.println(sql.toString());
            while (rs.next()) {
                //姓名、申请职位、申请状态、申请日期
                JobApply ja = new JobApply();
                ja.setApplyId(rs.getInt(1));
                ja.setApplyState(rs.getInt(2));
                ja.setApplyDate(rs.getTimestamp(3));
                Job job = new Job();
                job.setJobId(rs.getInt(4));
                job.setJobName(rs.getString(5));
                Applicant applicant = new Applicant();
                applicant.setApplicantId(rs.getInt(6));
                ResumeBasicinfo resume = new ResumeBasicinfo();
                resume.setBasicinfoId(rs.getInt(7));
                resume.setRealName(rs.getString(8));
                applicant.setResume(resume);
                ja.setJob(job);
                ja.setApplicant(applicant);
                list.add(ja);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeJDBC(rs, stmt, conn);
        }
        return list;
    }
    /**
	 * 保存职位申请信息
	 * 
	 * @param jobid
	 * @param applicantId
	 */
	public void save(String jobid, int applicantId) {
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstmt = null;
		String sql = "INSERT INTO tb_jobapply("
				+ "JOB_ID,APPLICANT_ID,APPLY_DATE,APPLY_STATE"
				+ ") VALUES(?,?,?,?)";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, Integer.parseInt(jobid));
			pstmt.setInt(2, applicantId);
			pstmt.setTimestamp(3, new Timestamp(new Date().getTime()));
			pstmt.setInt(4, 1);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeJDBC(null, pstmt, conn);
		}
	}

	/**
	 * 查询职位申请列表
	 * 
	 * @param applicantId
	 * @return
	 */
	public List<JobApply> getJobApplyList(int applicantId) {
		List<JobApply> list = new ArrayList<JobApply>();
		Connection conn = DBUtil.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT a.apply_id,a.apply_state,a.apply_date,j.job_id,j.job_name,c.company_id,c.company_name "
				+ "FROM tb_jobapply a , tb_job j ,tb_company c "
				+ "WHERE a.job_id=j.job_id and j.company_id=c.company_id "
				+ "and a.applicant_id = ?";
		try {
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, applicantId);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				JobApply ja = new JobApply();
				ja.setApplyId(rs.getInt(1));
				ja.setApplyState(rs.getInt(2));
				ja.setApplicantId(applicantId);
				ja.setApplyDate(rs.getTimestamp(3));
				Job job = new Job();
				job.setJobId(rs.getInt(4));
				job.setJobName(rs.getString(5));
				Company company = new Company();
				company.setCompanyId(rs.getInt(6));
				company.setCompanyName(rs.getString(7));
				job.setCompany(company);

				ja.setJob(job);
				list.add(ja);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.closeJDBC(rs, pstmt, conn);
		}
		return list;
	}

}